with dm_center_count_dt1 as( --创建临时表，每一次统计都从此临时表取数据，减少扫描文件次数，估量占内存 50M左右
select
	 waybill_no       --运单号
	,network_code     --网点编码
	,network_name     --网点名称
	,scan_time        --扫描日期
	,agency_area_name --代理商名称
	,agency_area_code --代理商编码
	,franchisee_name  --加盟商名称
	,franchisee_code  --加盟商编码
	,grid_code        --格口编码/异常编码
	,owned_venue_type --场地属性
	,equipment_code   --设备编码
	,operate_type     --操作模式
	,sorting_plan_code--分拣方案
	,supply_desk_code --供包台编码
	,grid_no          --格口编码
	from jms_ods.ass_scan_small_upper
	where
	--分区新增时间，今天要统计扫描时间的为t-1,t-2
    dt>='$startDay' and  dt<=date_add('$endDay',5)
	and date_format(scan_time,'yyyy-MM-dd')>='$startDay'
	and date_format(scan_time,'yyyy-MM-dd')<='$endDay'
),
with dm_center_count_dt1 as(
select
 scan_time         --扫描日期
,network_code      --网点编码
,null    as  equipment_code --设备编码
,null    as  operate_type
,null    as  sorting_plan_code
,null    as  supply_desk_code
,null    as  grid_code
,null    as  grid_no
,'1' cnt_type
,agency_area_name
,agency_area_code
,franchisee_name
,franchisee_code
,network_name
,cast(owned_venue_type as string) owned_venue_type
,center_sort_cnt sort_cnt
,center_zcsort_cnt zcsort_cnt
,center_ycsort_cnt ycsort_cnt
,null    as  equipment_cfsort_cnt
,null    as  sort_topcnt_min
,null    as  sort_toptime_min
,null    as  grid_name
,dt
from
(
select
scan_time
,franchisee_code    --加盟商编码
,franchisee_name   --加盟商名称
,agency_area_code  --代理商编码
,agency_area_name  --代理商名称
,network_code      --网点编码
,network_name      --网点名称
,owned_venue_type
,count(waybill_no) center_sort_cnt                             --中心分拣总量
,count(if(grid_code='111',waybill_no,null)) center_zcsort_cnt  --中心正常分拣量
,count(if(grid_code='111',null,waybill_no)) center_ycsort_cnt  --中心异常分拣量
,scan_time dt                        --时间分区
from
(   --去重
	select
	 waybill_no       --运单号
	,network_code     --网点编码
	,network_name     --网点名称
	,date_format(scan_time,'yyyy-MM-dd') scan_time  --扫描日期
	,agency_area_name --代理商名称
	,agency_area_code --代理商编码
	,franchisee_name  --加盟商名称
	,franchisee_code  --加盟商编码
	,grid_code        --格口编码/异常编码
	,owned_venue_type --场地属性
	from ass_scan_small_upper
	group by waybill_no,network_code,network_name,date_format(scan_time,'yyyy-MM-dd'),agency_area_name,agency_area_code ,franchisee_name ,franchisee_code
	,grid_code,owned_venue_type
)  scan_detail
group by franchisee_code,franchisee_name,agency_area_code,agency_area_name,
network_code,network_name,owned_venue_type,scan_time
) t ),
--供件台供件量统计
dm_supply_desk_count_dt as (
--字段顺序纠正
select
scan_time          --扫描日期
,network_code      --网点编码
,equipment_code    --设备编码
,operate_type
,sorting_plan_code
,supply_desk_code
,null    as  grid_code
,null    as  grid_no
,'3' cnt_type
,agency_area_name
,agency_area_code
,franchisee_name
,franchisee_code
,network_name
,cast(owned_venue_type as string) owned_venue_type
,supply_desk_cnt      sort_cnt
,null    as  zcsort_cnt
,null    as  ycsort_cnt
,null    as  equipment_cfsort_cnt
,supply_desk_topcnt    as  sort_topcnt_min
,supply_desk_toptime   as  sort_toptime_min
,null    as  grid_name
,dt
from
(--rk=1
select
network_code     --网点编码
,network_name     --网点名称
,date_format(scan_time,'yyyy-MM-dd')   scan_time   --扫描时间日期
,agency_area_name --代理商名称
,agency_area_code --代理商编码
,franchisee_name  --加盟商名称
,franchisee_code  --加盟商编码
,cast(owned_venue_type as string) owned_venue_type
,equipment_code --设备编码
,operate_type     --操作模式
,sorting_plan_code--分拣方案
,supply_desk_code --供包台，编码
,supply_desk_cnt --供包台总量
,supply_desk_cnt_min  supply_desk_topcnt --供件台峰值操作量
,scan_time  supply_desk_toptime   --扫描峰值时间
,date_format(scan_time,'yyyy-MM-dd') dt
from
(--排序
SELECT
 network_code     --网点编码
,network_name     --网点名称
,scan_time        --扫描时间到分钟
,agency_area_name --代理商名称
,agency_area_code --代理商编码
,franchisee_name  --加盟商名称
,franchisee_code  --加盟商编码
,owned_venue_type --场地属性
,equipment_code
,operate_type     --操作模式
,sorting_plan_code--分拣方案
,supply_desk_code --供包台，编码
,supply_desk_cnt supply_desk_cnt_min  --每分供件台供件数
,sum(supply_desk_cnt) over(partition by network_code,supply_desk_code,agency_area_code,franchisee_code,owned_venue_type,equipment_code,operate_type,sorting_plan_code,DATE_FORMAT(scan_time,'yyyy-MM-dd')) supply_desk_cnt --供包台总量
,ROW_NUMBER() over(partition by network_code,supply_desk_code,agency_area_code,franchisee_code,owned_venue_type,equipment_code,operate_type,sorting_plan_code,DATE_FORMAT(scan_time,'yyyy-MM-dd') order by supply_desk_cnt desc ) cnt_rk
from
(--每分钟统计供包台供件量
SELECT
 network_code     --网点编码
,network_name     --网点名称
,scan_time        --扫描时间到分钟
,agency_area_name --代理商名称
,agency_area_code --代理商编码
,franchisee_name  --加盟商名称
,franchisee_code  --加盟商编码
,owned_venue_type --场地属性
,equipment_code   --设备编码
,operate_type     --操作模式
,sorting_plan_code--分拣方案
,supply_desk_code --供包台，编码
,count(waybill_no) supply_desk_cnt
from
(   --去重
	select
	 waybill_no       --运单号
	,network_code     --网点编码
	,network_name     --网点名称
	,date_format(scan_time,'yyyy-MM-dd HH:mm') scan_time  --扫描日期
	,agency_area_name --代理商名称
	,agency_area_code --代理商编码
	,franchisee_name  --加盟商名称
	,franchisee_code  --加盟商编码
	,owned_venue_type --场地属性
	,equipment_code   --设备编码
	,operate_type     --操作模式
      ,sorting_plan_code--分拣方案
	,supply_desk_code --供包台，编码
	from ass_scan_small_upper
	group by waybill_no,network_code,network_name,date_format(scan_time,'yyyy-MM-dd HH:mm'),agency_area_name,agency_area_code ,franchisee_name ,franchisee_code ,owned_venue_type
      ,supply_desk_code,equipment_code,operate_type,sorting_plan_code
)  scan_detail
group by network_code,network_name,scan_time,agency_area_name,agency_area_code ,franchisee_name ,franchisee_code ,owned_venue_type
      ,supply_desk_code,equipment_code,operate_type,sorting_plan_code
)  scan_detail2
)  scan_detail3
where cnt_rk=1
)t ),
--异常统计
dm_sort_abnormal_count_dt as (
select
scan_time         --扫描日期
,network_code      --网点编码
,equipment_code --设备编码
,operate_type
,sorting_plan_code
,null    as  supply_desk_code
,grid_code
,null    as  grid_no
,'4' cnt_type
,agency_area_name
,agency_area_code
,franchisee_name
,franchisee_code
,network_name
,owned_venue_type
,abnormal_cnt sort_cnt
,null    as zcsort_cnt
,null    as ycsort_cnt
,null    as  equipment_cfsort_cnt
,null    as  sort_topcnt_min
,null    as  sort_toptime_min
,grid_name
,dt
from
(
SELECT
network_code      --网点编码
,network_name     --网点名称
,date_format(scan_time,'yyyy-MM-dd') scan_time  --扫描日期
,agency_area_name --代理商名称
,agency_area_code --代理商编码
,franchisee_name  --加盟商名称
,franchisee_code  --加盟商编码
,cast( owned_venue_type as string)  owned_venue_type
,equipment_code   --设备编码
,operate_type     --操作模式
,sorting_plan_code--分拣方案
,grid_code
,case when grid_code='995' then '三段码请求超时 (三段码查询失败)'
	when grid_code='996' then '三段码未配置(分拣方案未维护）'
	when grid_code='997' then '超最大循环圈数'
	when grid_code='998' then '拦截件'
	when grid_code='999' then '取消件'
  when grid_code='992' then '综合异常口'
      end as grid_name
,count(waybill_no) abnormal_cnt
,date_format(scan_time,'yyyy-MM-dd') dt
from
(
	select
	 waybill_no       --运单号
	,network_code     --网点编码
	,network_name     --网点名称
	,date_format(scan_time,'yyyy-MM-dd') scan_time  --扫描日期
	,agency_area_name --代理商名称
	,agency_area_code --代理商编码
	,franchisee_name  --加盟商名称
	,franchisee_code  --加盟商编码
	,owned_venue_type --场地属性
	,equipment_code   --设备编码
	,operate_type     --操作模式
      ,sorting_plan_code--分拣方案
      ,grid_code        --异常编码
	from ass_scan_small_upper
	where grid_code!='111'
	group by waybill_no,network_code,network_name,date_format(scan_time,'yyyy-MM-dd'),agency_area_name,agency_area_code ,franchisee_name ,franchisee_code ,owned_venue_type
      ,equipment_code,operate_type,sorting_plan_code,grid_code
)  scan_detail
group by network_code,network_name,scan_time,agency_area_name,agency_area_code ,franchisee_name ,franchisee_code ,owned_venue_type
      ,equipment_code,operate_type,sorting_plan_code,grid_code
)t ),
--格口落格统计量
dm_grid_falling_count_dt as (
select
scan_time          --扫描日期
,network_code      --网点编码
,equipment_code    --设备编码
,operate_type
,sorting_plan_code
,null    as  supply_desk_code
,null    as  grid_code
,grid_no
,'5' cnt_type
,agency_area_name
,agency_area_code
,franchisee_name
,franchisee_code
,network_name
,owned_venue_type
,grid_cnt sort_cnt
,null    as  zcsort_cnt
,null    as  ycsort_cnt
,null    as  equipment_cfsort_cnt
,null    as  sort_topcnt_min
,null    as  sort_toptime_min
,null    as  grid_name
,dt
from
(
SELECT
 network_code     --网点编码
,network_name     --网点名称
,scan_time        --扫描时间到分钟
,agency_area_name --代理商名称
,agency_area_code --代理商编码
,franchisee_name  --加盟商名称
,franchisee_code  --加盟商编码
,cast(owned_venue_type as string) owned_venue_type
,equipment_code --设备编码
,operate_type     --操作模式
,sorting_plan_code--分拣方案
,grid_no --供包台，编码
,count(waybill_no) grid_cnt
,date_format(scan_time,'yyyy-MM-dd') dt
from
(
	select
	 waybill_no       --运单号
	,network_code     --网点编码
	,network_name     --网点名称
	,date_format(scan_time,'yyyy-MM-dd') scan_time  --扫描日期
	,agency_area_name --代理商名称
	,agency_area_code --代理商编码
	,franchisee_name  --加盟商名称
	,franchisee_code  --加盟商编码
	,owned_venue_type --场地属性
	,equipment_code   --设备编码
	,operate_type     --操作模式
   ,sorting_plan_code--分拣方案
	,grid_no         --格口编码
	from ass_scan_small_upper
  group by waybill_no,network_code,network_name,date_format(scan_time,'yyyy-MM-dd'),agency_area_name,agency_area_code ,franchisee_name ,franchisee_code ,owned_venue_type
      ,grid_no,equipment_code,operate_type,sorting_plan_code
)  scan_detail
group by network_code,network_name,scan_time,agency_area_name,agency_area_code ,franchisee_name ,franchisee_code ,owned_venue_type
      ,grid_no,equipment_code,operate_type,sorting_plan_code
) t ),
dm_equipment_sort_count_dt  as
(
select
scan_time         --扫描日期
,network_code     --网点编码
,equipment_code   --设备编码
,operate_type
,sorting_plan_code
,null    as  supply_desk_code
,null    as  grid_code
,null    as  grid_no
,'2' cnt_type
,agency_area_name
,agency_area_code
,franchisee_name
,franchisee_code
,network_name
,owned_venue_type
,equipment_sort_cnt    sort_cnt
,equipment_zcsort_cnt  zcsort_cnt
,equipment_ycsort_cnt  ycsort_cnt
,equipment_cfsort_cnt
,equipment_sort_topcnt sort_topcnt_min
,equipment_sort_toptime sort_toptime_min
,null    as  grid_name
,dt
from
( --筛检出 分拣总量最高的那一条数据
select
 network_code     --网点编码
,network_name     --网点名称
,scan_time        --扫描日期
,agency_area_name --代理商名称
,agency_area_code --代理商编码
,franchisee_name  --加盟商名称
,franchisee_code  --加盟商编码
,cast( owned_venue_type as string ) owned_venue_type
,equipment_code --设备编码
,operate_type     --操作模式
,sorting_plan_code--分拣方案
,equipment_sort_cnt    --中心分拣总量
,equipment_zcsort_cnt  --中心正常分拣量
,equipment_ycsort_cnt  --中心异常分拣量
,equipment_cfsort_cnt      --设备错分量
,min_sortcnt       equipment_sort_topcnt   --设备扫描峰值
,scan_time_min     equipment_sort_toptime  --设备扫描峰值时间 'yyyy-MM-dd  HH:mm'
,date_format(scan_time,'yyyy-MM-dd') dt
from
(   --筛检出 分拣总量最高的那一条数据 排序
	select
	network_code     --网点编码
	,network_name     --网点名称
	,date_format(scan_time,'yyyy-MM-dd')  scan_time--扫描日期
	,agency_area_name --代理商名称
	,agency_area_code --代理商编码
	,franchisee_name  --加盟商名称
	,franchisee_code  --加盟商编码
	,grid_code        --格口编码/异常编码
	,owned_venue_type --场地属性
	,equipment_code --设备编码
	,operate_type     --操作模式
	,sorting_plan_code--分拣方案
	,equipment_sort_cnt      --设备分拣总量
	,equipment_cfsort_cnt   --设备错分总量
	,equipment_zcsort_cnt
	,equipment_ycsort_cnt
	,min_sortcnt
	,scan_time_min
	,ROW_NUMBER() over(partition by  network_code,equipment_code,scan_time,agency_area_code,franchisee_code,owned_venue_type,operate_type,sorting_plan_code order by min_sortcnt desc) rk
	from
	(   --取一天的分拣总量和错分量
		select
		 network_code     --网点编码
		,network_name     --网点名称
		,date_format(scan_time,'yyyy-MM-dd')  scan_time--扫描日期
		,agency_area_name --代理商名称
		,agency_area_code --代理商编码
		,franchisee_name  --加盟商名称
		,franchisee_code  --加盟商编码
		,grid_code        --格口编码/异常编码
		,owned_venue_type --场地属性
		,equipment_code --设备编码
		,operate_type     --操作模式
		,sorting_plan_code--分拣方案
		,sum(min_sortcnt_type) over(partition by network_code,equipment_code,date_format(scan_time,'yyyy-MM-dd'),agency_area_code,franchisee_code,owned_venue_type,operate_type,sorting_plan_code)     equipment_sort_cnt      --设备分拣总量
		,sum(is_errsort_min) over(partition by network_code,equipment_code,date_format(scan_time,'yyyy-MM-dd'),agency_area_code,franchisee_code,owned_venue_type,operate_type,sorting_plan_code)  equipment_cfsort_cnt   --设备错分总量
		,sum(if(grid_code='111',min_sortcnt_type,0)) over(partition by  network_code,equipment_code,date_format(scan_time,'yyyy-MM-dd'),agency_area_code,franchisee_code,owned_venue_type,operate_type,sorting_plan_code) equipment_zcsort_cnt
		,sum(if(grid_code='111',0,min_sortcnt_type)) over(partition by  network_code,equipment_code,date_format(scan_time,'yyyy-MM-dd'),agency_area_code,franchisee_code,owned_venue_type,operate_type,sorting_plan_code) equipment_ycsort_cnt
		,sum(min_sortcnt_type) over(partition by  network_code,equipment_code,scan_time,agency_area_code,franchisee_code,owned_venue_type,operate_type,sorting_plan_code) min_sortcnt
		,scan_time scan_time_min
		from
		(   --group by
			select
			network_code     --网点编码
			,network_name     --网点名称
			,scan_time  --扫描日期
			,agency_area_name --代理商名称
			,agency_area_code --代理商编码
			,franchisee_name  --加盟商名称
			,franchisee_code  --加盟商编码
			,grid_code        --格口编码/异常编码
			,owned_venue_type --场地属性
			,equipment_code   --设备编码
			,operate_type     --操作模式
			,sorting_plan_code--分拣方案
			,sum(is_errsort)   is_errsort_min        --是否错分 分正常异常的类型
			,count(waybill_no) min_sortcnt_type      --设备每分钟的操作量 分正常异常的类型
			from
			(   --与错分表join
				select
				 waybill_no       --运单号
				,network_code     --网点编码
				,network_name     --网点名称
				,scan_time        --扫描时间
				,agency_area_name --代理商名称
				,agency_area_code --代理商编码
				,franchisee_name  --加盟商名称
				,franchisee_code  --加盟商编码
				,grid_code        --格口编码/异常编码
				,owned_venue_type --场地属性
				,equipment_code --设备编码
				,operate_type     --操作模式
				,sorting_plan_code--分拣方案
				,if(miss_sort.billcode is not null,1,0)  is_errsort --是否错分
				from
				(
					select
					 waybill_no       --运单号
					,network_code     --网点编码
					,network_name     --网点名称
					,date_format(scan_time,'yyyy-MM-dd HH:mm') scan_time  --扫描日期
					,agency_area_name --代理商名称
					,agency_area_code --代理商编码
					,franchisee_name  --加盟商名称
					,franchisee_code  --加盟商编码
					,grid_code        --格口编码/异常编码
					,owned_venue_type --场地属性
					,equipment_code --设备编码
					,operate_type     --操作模式
					,sorting_plan_code--分拣方案
					from ass_scan_small_upper
         				group by waybill_no,network_code,network_name,date_format(scan_time,'yyyy-MM-dd HH:mm'),agency_area_name,agency_area_code ,franchisee_name ,franchisee_code ,grid_code,owned_venue_type,equipment_code ,operate_type ,sorting_plan_code--分拣方案
				) scan_detail
				left JOIN
				(
					select
					billcode
					from jms_ods.tab_report_errorseparatedetail
					where dt>='$startDay' and  dt<='$endDay'
					group by billcode
				) miss_sort
				on scan_detail.waybill_no=miss_sort.billcode
			) equipment_miss_sort
			 group by network_code,network_name,scan_time,agency_area_name,agency_area_code ,franchisee_name ,franchisee_code ,
			 grid_code,owned_venue_type,equipment_code ,operate_type ,sorting_plan_code--分拣方案
		) equipment_miss_sort_top
   ) equipment_sort_cnt
) equipment_miss_sort_top2
where rk=1
) t )

insert overwrite table jms_dm.dm_center_sort_count_dt partition(dt)
select
scan_time, network_code, equipment_code, operate_type, sorting_plan_code, supply_desk_code, grid_code, grid_no, cnt_type, agency_area_name, agency_area_code, franchisee_name, franchisee_code, network_name, owned_venue_type, sort_cnt, zcsort_cnt, ycsort_cnt, equipment_cfsort_cnt, sort_topcnt_min, sort_toptime_min, grid_name,
ROW_NUMBER() over(partition by scan_time order by scan_time) id, dt
from
(
select * from dm_center_count_dt1
union all
select * from dm_supply_desk_count_dt
union all
select * from dm_sort_abnormal_count_dt
union all
select * from dm_grid_falling_count_dt
union all
select * from dm_equipment_sort_count_dt
)t